package com.kkb.dao.impl;

import com.kkb.bean.Courier;
import com.kkb.bean.User;
import com.kkb.dao.BaseAdminDao;
import com.kkb.dao.BaseCourierDao;
import com.kkb.utils.DateFormatUtil;
import com.kkb.utils.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class CourierDaoMysql implements BaseCourierDao {

    private static final String SQL_UPDATE_LOGIN_TIME = "UPDATE COURIER SET LOGINTIME = NOW() WHERE CPHONE =?";
    private static final String SQL_LOGIN = "SELECT * FROM COURIER WHERE CNAME=? AND CPASSWORD =?";
    private static final String SQL_IS_EXIST = "SELECT * FROM COURIER WHERE CPHONE =?";
    private static final String SQL_UPDATE = "UPDATE COURIER SET CNAME=?,CPHONE=?,CCARD=?,CPASSWORD=? WHERE ID=?";
    private static final String SQL_FIND_BY_PHONE = "SELECT * FROM COURIER WHERE CPHONE=?";
    private static final String SQL_INSERT = "INSERT INTO COURIER(CNAME,CPHONE,CCARD,CPASSWORD,CCOUNT,CREATETIME) VALUES(?,?,?,?,0,NOW())";
    private static final String SQL_DELETE = "DELETE FROM COURIER WHERE ID = ? ";
    private static final String SQL_FIND_ALL = "SELECT * FROM COURIER";
    private static final String SQL_FIND_BY_LIMIT = "SELECT * FROM COURIER LIMIT ?,?";
    private static final String SQL_CONSOLE = "SELECT COUNT(ID) DATA1 , COUNT(TO_DAYS(CREATETIME) = TO_DAYS(NOW()) OR NULL)  DATA2 FROM COURIER ";

    /**
     * 查询日新增快递员人数
     * 总的快递小哥数量
     *
     * @return map
     */
    @Override
    public Map<String, Integer> console() {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        Map<String,Integer> map = new HashMap<>();
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);

            // 4. 执行
            rs = preparedStatement.executeQuery();
            // 5. 查询结果
            if (rs.next()){
                map.put("data1",rs.getInt("DATA1"));
                map.put("data2",rs.getInt("DATA2"));
            }
            return  map;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,rs);
        }
        return null;
    }

    /**
     * 快递员微信登录
     *
     * @param phone
     * @param password
     * @return
     */
    @Override
    public Courier login(String phone, String password) {
        return null;
    }

    /**
     * 修改快递员的登录信息
     *
     * @param phone 快递员的名字
     * @return
     */
    @Override
    public boolean updateLoginInfo(String phone) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_UPDATE_LOGIN_TIME);
            // 3. 填充参数
            preparedStatement.setString(1,phone);
            // 4. 执行
            return preparedStatement.executeUpdate() > 0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 查询快递员列表信息
     *
     * @param limit      是否分页
     * @param offset     从那条数据开始
     * @param pageNumber 数据的条数
     * @return
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            // 2. 预编译sql语句
            if (limit){
                preparedStatement = connection.prepareStatement(SQL_FIND_BY_LIMIT);
                preparedStatement.setInt(1,offset);
                preparedStatement.setInt(2,pageNumber);
            } else{
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL);
            }
            // 4. 执行
            rs = preparedStatement.executeQuery();
            // 5. 查询结果
            List<Courier> list = new ArrayList<>();
            while (rs.next()){
                int id = rs.getInt("id");
                String cName = rs.getString("cname");
                String cPhone = rs.getString("cphone");
                String cCard = rs.getString("ccard");
                String cPassword = rs.getString("cpassword");
                int cCount = rs.getInt("ccount");
                Date createTime = rs.getDate("createtime");
                Date loginTime = rs.getDate("logintime");
                Courier courier = new Courier(id,cName,cPhone,cCard,cPassword,cCount,createTime,loginTime);
                list.add(courier);
            }
            return  list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,rs);
        }
        return null;
    }

    /**
     * 根据手机号查询快递员信息
     *
     * @param phone
     * @return
     */
    @Override
    public Courier findByPhone(String phone) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_PHONE);
            preparedStatement.setString(1,phone);
            // 4. 执行
            rs = preparedStatement.executeQuery();
            // 5. 查询结果
            Courier courier = null;
            if (rs.next()){
                int id = rs.getInt("id");
                String cName = rs.getString("cname");
                String cPhone = rs.getString("cphone");
                String cCard = rs.getString("ccard");
                String cPassword = rs.getString("cpassword");
                int cCount = rs.getInt("ccount");
                Date createTime = rs.getDate("createtime");
                Date loginTime = rs.getDate("logintime");
                courier = new Courier(id,cName,cPhone,cCard,cPassword,cCount,createTime,loginTime);
            }
            return  courier;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,rs);
        }
        return null;
    }

    /**
     * 添加一个快递员
     *
     * @param courier 快递员信息
     * @return 添加是否成功
     */
    @Override
    public boolean insert(Courier courier) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_INSERT);
            // 3. 填充参数
            preparedStatement.setString(1, courier.getcName());
            preparedStatement.setString(2,courier.getcPhone());
            preparedStatement.setString(3,courier.getcCard());
            preparedStatement.setString(4,courier.getcPassword());
            // 4. 执行
            return preparedStatement.executeUpdate() > 0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 根据id修改快递员信息
     *
     * @param id      快递员id
     * @param courier 快递员信息
     * @return 修改是否成功
     */
    @Override
    public boolean update(int id, Courier courier) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            // 3. 填充参数
            preparedStatement.setString(1, courier.getcName());
            preparedStatement.setString(2,courier.getcPhone());
            preparedStatement.setString(3,courier.getcCard());
            preparedStatement.setString(4,courier.getcPassword());
            preparedStatement.setInt(5,id);
            // 4. 执行
            return preparedStatement.executeUpdate() > 0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 根据id删除快递员
     *
     * @param id
     * @return
     */
    @Override
    public boolean delete(int id) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            // 3. 填充参数
            preparedStatement.setInt(1,id);
            // 4. 执行
            return preparedStatement.executeUpdate() > 0 ? true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,null);
        }
        return false;
    }

    /**
     * 判断 该手机号是否已经存在
     *
     * @param phone
     * @return
     */
    @Override
    public boolean isExist(String phone) {
        // 1. 获取连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        try {
            // 2. 预编译sql语句
            preparedStatement = connection.prepareStatement(SQL_IS_EXIST);
            // 3. 填充参数
            preparedStatement.setString(1,phone);
            // 4. 执行
            rs  = preparedStatement.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. 释放资源
            DruidUtil.close(connection,preparedStatement,rs);
        }
        return false;
    }
}
